package com.atguigu.phoenixjdbctest;

import org.apache.phoenix.queryserver.client.ThinClientUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

public class PhoenixJDBCtest {

    private Connection connection;
    private PreparedStatement preparedStatement;


    /**
     * 初始化连接操作
     */
    @Before
    public void init() throws Exception {
        //1.加载Driver
        Class.forName("org.apache.phoenix.queryserver.client.Driver");
        //2.配置并连接Phoenix
        String url = ThinClientUtil.getConnectionUrl("hadoop102", 8765);
        connection = DriverManager.getConnection(url);
        //3.设置commit自动提交
        connection.setAutoCommit(true);
    }

    @After
    public void close() throws Exception {
        //4.关闭
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }

    @Test
    public void createTable() throws Exception {

        //3.预编译
        preparedStatement = connection.prepareStatement("create table hahahahaha" + "(id varchar primary key,"
                + "info1.name varchar,info2.age varchar) column_encoded_bytes=0");
        //4.执行
        preparedStatement.execute();
    }

    /**
     * 插入单条数据
     *
     * @throws SQLException
     */
    @Test
    public void insert() throws SQLException {
        //4.预编译
        preparedStatement = connection.prepareStatement("upsert into person" +
                " values(?,?,?)");
        preparedStatement.setString(1, "1001");
        preparedStatement.setString(2, "wzx");
        preparedStatement.setString(3, "18");
        //5.执行
        preparedStatement.executeUpdate();
    }

    /**
     * 插入批量数据
     */
    @Test
    public void insertBatch() throws SQLException {

        //4.预编译
        String sql = "upsert into person values(?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 1200; i++) {
            preparedStatement.setString(1, "100" + i);
            preparedStatement.setString(2, "wzx-" + i);
            preparedStatement.setString(3, (20 + i) + "");
            //5.添加进批次
            preparedStatement.addBatch();
            if (i % 500 == 0) {
                //6.执行批次
                preparedStatement.executeBatch();
                //7.清空批次
                preparedStatement.clearBatch();
            }
        }
        //8.执行最后一个批次
        preparedStatement.executeBatch();
    }

    /**
     * 查询数据
     */
    @Test
    public void query() throws SQLException {
        //预编译
        String sql = "select * from person where id > ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"1001");
        //执行
        ResultSet resultSet = preparedStatement.executeQuery();
        //展示
        while(resultSet.next()){
            String id = resultSet.getString("id");
            String name = resultSet.getString("name");
            String age = resultSet.getString("age");
            System.out.println("id="+id+" ,name="+name+" ,age="+age);
        }
    }

    /**
     * 删除数据
     */
    @Test
    public void delete() throws SQLException {
        String sql = "delete from person where id > ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //参数赋值
        preparedStatement.setString(1,"1001");
        //执行
        preparedStatement.executeUpdate();
    }

    /**
     * 删除表
     * @throws Exception
     */
    @Test
    public void drop() throws Exception{
        String sql = "drop table person";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.execute();
    }
}
